﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Data;

namespace DBTool
{
    class DBConnect : Singleton< DBConnect >
    {
        private MySqlConnection _connection;
        private string _dbScriptSequenceColumn = "script_seq";

        public DBConnect()
        { }

        //Initialize values
        public void Connect( DBConnectInfo connectInfo ) 
        {
            if (null == connectInfo)
            {
                MessageBox.Show("Connect Info Is Null", "Connect()", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            string connectionString = "SERVER=" + connectInfo._serverIP + ";" + "DATABASE=" + connectInfo._databaseName + ";" 
                                + "UID=" + connectInfo._userID + ";" + "PASSWORD=" + connectInfo._password + ";";

            _connection = new MySqlConnection(connectionString);
            if (false == OpenConnection())
                MessageBox.Show("DB Connect Fail", "DB_Connect_Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
     
            CloseConnection();
        }

        private bool OpenConnection()
        {
            try
            {
                _connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server.  Contact administrator", "DB_Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
                        break;

                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again", "DB_Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        break;
                }
                return false;
            }
        }

        //Close connection
        private bool CloseConnection()
        {
            try
            {
                _connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message, "Close Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        public bool Insert( string query, string tableName )
        {
            if (null == _connection)
            {
                MessageBox.Show( "Connection Is NULL. Insert() ");
                return false;
            }

            //string query = "INSERT INTO example (name, phone, id, city) VALUES('John Smith', '010-7128-5454', 'titetoto123', 'seoul')";

            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, _connection);
                
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show( tableName + " DB Insert Error \r\n" + ex.Message + "\n 문의 : 이대남", "DB Insert_Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }

                this.CloseConnection();
            }

            return true;
        }

        public void DeleteTable( string tableName )
        {
            if (null == _connection)
            {
                MessageBox.Show("Connection Is NULL. Delete() ");
                return;
            }

            string query = "truncate table " + tableName;

            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, _connection);

                cmd.ExecuteNonQuery();

                this.CloseConnection();
            }
        }

        public string[] GetDBTableList()
        {
            if (null == _connection)
                return null;

            MySqlCommand cmd = _connection.CreateCommand();
            cmd.CommandText = "show tables;";

            OpenConnection();

            List<string> tableList = new List<string>();

            MySqlDataReader Reader = cmd.ExecuteReader();
            while (Reader.Read())
            {
                for (int i = 0; i < Reader.FieldCount; i++)
                    tableList.Add(Reader.GetValue(i).ToString());
            }

            CloseConnection();
            
            return tableList.ToArray();
        }

        public string[] GetTableTitles(string tableName)
        {
            if (null == _connection)
                return null;

            List<string> tableColumnList = new List<string>();

            OpenConnection();

            MySqlCommand cmd = _connection.CreateCommand();
            cmd.CommandText = "select * from " + tableName + ";";
            cmd.Connection = _connection;

            try
            {
                MySqlDataReader reader = cmd.ExecuteReader();
                DataTable dt = new DataTable();

                dt.Load( reader );

                // columnCount = 1, reason : exclude script sequence
                for( int i = 1 ; i < dt.Columns.Count ; ++i )
                    tableColumnList.Add( dt.Columns[i].Caption );
            }
            catch( MySqlException except )
            {
                CloseConnection();
                return null;
            }

            CloseConnection();

            return tableColumnList.ToArray();
        }

        public void BulkInsert( string filePath )
        {
            if (null == _connection)
                return;

            var bulkInsert = new MySqlBulkLoader(_connection);
            bulkInsert.TableName = Path.GetFileNameWithoutExtension( filePath );
            bulkInsert.FieldTerminator = ",";
            bulkInsert.LineTerminator = "\n";
            bulkInsert.FileName = filePath;
            bulkInsert.NumberOfLinesToSkip = 1;  // TitleRowSkip
            bulkInsert.Load();
        }

        public string GetTableValues( string dbTableName )
        {
            OpenConnection();

            MySqlCommand cmd = _connection.CreateCommand();
            cmd.CommandText = "select * from " + dbTableName + " ORDER BY " + _dbScriptSequenceColumn + " ASC;";
            cmd.Connection = _connection;

            MySqlDataReader reader = cmd.ExecuteReader();


            //
            //
            //
            DataTable dt = new DataTable();
            dt.Load( reader );

            string dbTableValues = "";

            // columnCount = 1, reason : exclude script sequence
            foreach( DataRow dataRow in dt.Rows )
            {
                string row = String.Join(",", dataRow.ItemArray );
                string[] rowColums = row.Split(',');
                
                for( int columnCount = 1 ; columnCount < rowColums.Length ; ++columnCount )
                {
                    string value = true == string.IsNullOrEmpty( rowColums[ columnCount ] ) ? "null" : rowColums[ columnCount ] ;
                    
                    /*
                    if( typeof(float) == dt.Columns[ columnCount ].DataType )
                    {
                        float floatValue = float.Parse( value );
                        value = floatValue.ToString("F0");
                    }
                    */
                    dbTableValues += value;
                    dbTableValues += ",";
                }

                dbTableValues += "\r\n";
            }

            CloseConnection();

            return dbTableValues;
        }
    }
}
